import express from 'express';
import sql from '../../sql.js';
const router = express.Router();
// 查询分类数据
router.get('/searchCategory', async (req, res) => {
    let query = 'SELECT * FROM category';
    let params = [];
    const type = req.query.type;
    if (type) {
        query += ' WHERE type =?';
        params.push(type);
    }
    try {
        const [data] = await sql.query(query, params);

        // 为每个分类数据计算并添加 totalNum 字段
        for (let category of data) { 
            if (category.type) {
                const categoryId = category.ID;
                let countQuery;
                let associatedTable = category.type == 3 ? 'file' : 'article';
                countQuery = `SELECT COUNT(*) AS totalNum FROM ${associatedTable} WHERE category_id =?`;
                const [countResult] = await sql.query(countQuery, [categoryId]);
                category.totalNum = countResult[0].totalNum || 0;
            } else {
                let countQuery = 'SELECT COUNT(*) AS totalNum FROM article WHERE category_id =1';
                const [countResult1] = await sql.query(countQuery);
                category.totalNum = countResult1[0].totalNum || 0;
                countQuery = 'SELECT COUNT(*) AS totalNum FROM file WHERE category_id =1'
                const [countResult2] = await sql.query(countQuery);
                category.totalNum += countResult2[0].totalNum || 0;
            }
            
        }
        

        if (data.length > 0) {
            res.send({
                status: 'success',
                message: '分类数据查询成功',
                data: data
            });
        } else {
            res.send({
                status: 'error',
                message: '未找到对应类型的分类数据',
                errorCode: 404
            });
        }
    } catch (error) {
        console.error('查询错误：', error);
        res.send({
            status: 'error',
            message: '服务器内部错误',
            errorCode: 500
        });
    }
});

// 添加分类
router.post('/addCategory', async (req, res) => {
    const categoryName = req.body.categoryName;
    const groupType = req.body.groupType;
    if (!categoryName ||!groupType) {
        return res.send({
            status: 'error',
            message: '缺少必要参数',
            errorCode: 400
        });
    }
    const query = 'INSERT INTO category (category_name, type) VALUES (?,?)';
    try {
        // 检查name是否已存在
        const checkQuery = 'SELECT * FROM category WHERE category_name =?';
        const [existingShares] = await sql.query(checkQuery, [categoryName]);
        if (existingShares.length > 0) {
            return res.send({
                status: 'error',
                message: '分类名已存在',
                errorCode: 409
            });
        }
        await sql.query(query, [categoryName, groupType]);
        res.send({
            status: 'success',
            message: '分类添加成功'
        });
    } catch (error) {
        console.error('添加错误：', error);
        res.send({
            status: 'error',
            message: '服务器内部错误',
            errorCode: 500
        });
    }
});

// 修改分类所属类型
router.put('/updateCategoryType', async (req, res) => {
  const categoryIds = req.body.categoryIds; // 包含一个或多个要更换分组的分类 ID 数组
  const newCategoryId = req.body.newCategoryId; // 新的分类 ID
  if (!categoryIds ||!newCategoryId) {
      return res.send({
          status: 'error',
          message: '缺少必要参数',
          errorCode: 400
      });
  }
  const placeholders = categoryIds.map(() => '?').join(',');
  const query = `UPDATE category SET type =? WHERE ID IN (${placeholders})`;
  const values = [newCategoryId,...categoryIds];
  try {
      await sql.query(query, values);
      res.send({
          status: 'success',
          message: '分类更新成功'
      });
  } catch (error) {
      console.error('更新错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

// 修改分类名称
router.put('/updateCategoryName', async (req, res) => {
  const categoryId = req.body.categoryId;
  const newCategoryName = req.body.newCategoryName;
  if (!categoryId ||!newCategoryName) {
      return res.send({
          status: 'error',
          message: '缺少必要参数',
          errorCode: 400
      });
  }
  // 检查新名称是否重复
  const checkQuery = 'SELECT * FROM category WHERE category_name =? AND ID!=?';
  const [existingCategories] = await sql.query(checkQuery, [newCategoryName, categoryId]);
  if (existingCategories.length > 0) {
      return res.send({
          status: 'error',
          message: '分类名已存在',
          errorCode: 409
      });
  }
  const updateQuery = 'UPDATE category SET category_name =? WHERE ID =?';
  try {
      await sql.query(updateQuery, [newCategoryName, categoryId]);
      res.send({
          status: 'success',
          message: '分类名称修改成功'
      });
  } catch (error) {
      console.error('更新错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

// 删除分类
router.delete('/deleteCategory', async (req, res) => {
  const categoryIds = req.body.categoryIds;
  if (!categoryIds || categoryIds.length === 0) {
      return res.send({
          status: 'error',
          message: '缺少必要参数',
          errorCode: 400
      });
  }
  const placeholders = categoryIds.map(() => '?').join(',');
  const deleteCategoryQuery = `DELETE FROM category WHERE ID IN (${placeholders})`;
  const updateFileQuery = 'UPDATE file SET category_id = 1 WHERE category_id =?';
  const updateArticleQuery = 'UPDATE article SET category_id = 1 WHERE category_id =?';
        
  try {
    await sql.beginTransaction();
    await sql.query(deleteCategoryQuery, categoryIds);
    await sql.query(updateFileQuery, categoryIds);
    await sql.query(updateArticleQuery, categoryIds);
    await sql.commit();
      res.send({
          status: 'success',
          message: '分类删除成功'
      });
  } catch (error) {
      console.error('删除错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

export default router;

